﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace HmLab;

/// <summary>
/// <see cref="LbReadLargeExcel.Reader"/>
/// </summary>
[Obsolete]
internal class LargeExcelReaderLb {

  public LgExcelResult Read(string path, int sheetIndex, string headerRow, string[] col) {
    using SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false);
    WorkbookPart? bookPart = doc.WorkbookPart;
    Debug.Assert(bookPart != null);

    Dictionary<string, string> targetSheet = GetSheet(bookPart, sheetIndex);
    WorksheetPart sheetPart = bookPart.WorksheetParts
      .Where(p => bookPart.GetIdOfPart(p).Equals(targetSheet["id"]))
      .Single();

    using OpenXmlReader reader = OpenXmlReader.Create(sheetPart);
    while (reader.Read()) {
      //Console.WriteLine(reader.ElementType);

      if (reader.ElementType == typeof(Row) && reader.IsStartElement) {
        string? rowNum = GetAttr(reader, "r");
        if (headerRow.Equals(rowNum)) {
          break;
        }
      }
    }

    Dictionary<string, ColRef> headerDict = ReadHeader(reader, bookPart);
    HashSet<string> colFilter = col.Select(c => headerDict[c].Letter).ToHashSet();

    Console.WriteLine("----------------");
    Stopwatch watch = Stopwatch.StartNew();

    // dimen预分配是否能提升性能？
    List<RowRead> rowList = [];
    while (reader.Read()) {
      if (reader.IsEndElement) {
        continue;
      }

      if (reader.ElementType == typeof(Row)) {
        //FIXME: 先写死，后面抽dataRow
        if ("4".Equals(GetAttr(reader, "r"))) {
          continue;
        }

        RowRead row = ReadDataRow(reader, colFilter);
        rowList.Add(row);
      }
    }

    watch.Stop();
    Console.WriteLine($"数据行数：{rowList.Count}，用时：{watch.ElapsedMilliseconds}ms，{path}");

    var getStrKey = (RowRead r) => r.Cells
      .Where(c => c.StrKey >= 0)
      .Select(c => c.StrKey);

    int sstMax = rowList.SelectMany(getStrKey).DefaultIfEmpty().Max();
    string[] sst = ReadSharedString(bookPart, sstMax);
    Console.WriteLine("--ssssssssssss_sst " + sstMax);
    //foreach (var s in sst) {
    //  Console.WriteLine(s);
    //}

    Dictionary<string, int> resultHeader = headerDict.Values
      .Where(c => colFilter.Contains(c.Letter))
      .OrderBy(c => c.Letter.Length)
      .ThenBy(c => c.Letter)
      .Select((c, i) => (name: c.Val, col: i))
      .ToDictionary(kv => kv.name, kv => kv.col);

    LgExcelRow[] resultRows = rowList
      .Select(r => MakeResultRow(r, sst))
      .ToArray();

    return new(resultHeader, resultRows);
  }

  Dictionary<string, string> GetSheet(WorkbookPart bookPart, int index) {
    using OpenXmlReader reader = OpenXmlReader.Create(bookPart);
    int curPos = 1;

    while (reader.Read()) {
      if (reader.IsEndElement) {
        continue;
      }

      Console.WriteLine(reader.ElementType);

      if (reader.ElementType == typeof(Sheet)) {
        foreach (var attr in reader.Attributes) {
          Console.WriteLine(" - " + attr.LocalName + " " + attr.Value);
        }

        if (curPos == index + 1) {
          return reader.Attributes
            .ToDictionary(a => a.LocalName, a => a.Value ?? "");
        }

        curPos++;
      }
    }

    return [];
  }

  string? GetAttr(OpenXmlReader reader, string name) {
    return reader.Attributes
      .Where(a => a.LocalName.Equals(name))
      .Select(a => a.Value)
      .FirstOrDefault();
  }

  string GetCol(OpenXmlReader reader) {
    string? refer = GetAttr(reader, "r");
    Debug.Assert(refer != null);
    return Regex.Replace(refer, @"\d+$", "");
  }

  record ColRef(string Letter, string Val);
  Dictionary<string, ColRef> ReadHeader(OpenXmlReader reader, WorkbookPart bookPart) {
    List<(string col, string text, int strKey)> colList = [];
    string colLetter = "";

    while (reader.Read()) {
      if (reader.IsEndElement) {
        if (reader.ElementType == typeof(Row)) {
          break;
        }
        continue;
      }

      //Console.WriteLine(reader.ElementType);
      if (reader.ElementType == typeof(Cell)) {
        colLetter = GetCol(reader);
        //Console.WriteLine(" - " + colLetter);
        continue;
      }

      if (reader.ElementType == typeof(CellValue)) {
        string valText = reader.GetText();
        //Console.WriteLine(" - " + valText);

        int strKey = int.Parse(valText);
        colList.Add((colLetter, valText, strKey));
        continue;
      }

      if (reader.ElementType == typeof(Text)) {
        string valText = reader.GetText();
        colList.Add((colLetter, valText, -1));
        continue;
      }
    }

    int sstMax = colList.Select(c => c.strKey).Max();
    //Console.WriteLine(sstMax);
    string[] sst = ReadSharedString(bookPart, sstMax);

    var getVal = (string text, int strKey) => strKey < 0 ? text : sst[strKey];
    return colList
      .Select(c => new ColRef(c.col, getVal(c.text, c.strKey)))
      .Where(c => !string.IsNullOrWhiteSpace(c.Val))
      .ToDictionary(c => c.Val, c => c);
  }

  string[] ReadSharedString(WorkbookPart bookPart, int max) {
    SharedStringTablePart? sstPart = bookPart.SharedStringTablePart;
    Debug.Assert(sstPart != null);

    List<string> result = [];
    int index = 0;

    using OpenXmlReader reader = OpenXmlReader.Create(sstPart);
    while (reader.Read()) {
      if (reader.IsEndElement) {
        continue;
      }

      //Console.WriteLine(reader.ElementType);
      if (reader.ElementType != typeof(Text)) {
        continue;
      }

      result.Add(reader.GetText());
      if (++index > max) {
        break;
      }
    }

    return result.ToArray();
  }

  record CellRead(string Type, string Text, int StrKey);
  record RowRead(string Num, CellRead[] Cells);
  RowRead ReadDataRow(OpenXmlReader reader, HashSet<string> colFilter) {
    List<CellRead> colResult = new(colFilter.Count);
    string cellType = "";

    var skip = (Type type) => {
      while (reader.Read()) {
        if (reader.IsEndElement && reader.ElementType == type) {
          return;
        }
      }
    };

    string? rowNum = GetAttr(reader, "r");
    Debug.Assert(rowNum != null);

    while (reader.Read()) {
      if (reader.IsEndElement) {
        if (reader.ElementType == typeof(Row)) {
          break;
        }
        continue;
      }

      //Console.WriteLine(reader.ElementType);
      if (reader.ElementType == typeof(Cell)) {
        string colRef = GetCol(reader);
        if (!colFilter.Contains(colRef)) {
          skip(typeof(Cell));
          continue;
        }

        cellType = GetAttr(reader, "t") ?? "n";
        //Console.WriteLine(" - " + colRef + " " + cellType);
        continue;
      }

      if (reader.ElementType == typeof(CellValue)) {
        string valText = reader.GetText();
        //Console.WriteLine(" - " + valText);

        int strKey = cellType.Equals("s") ? int.Parse(valText) : -1;
        colResult.Add(new(cellType, valText, strKey));

        if (colResult.Count >= colFilter.Count) {
          skip(typeof(Row));
          break;
        }

        continue;
      }

      if (reader.ElementType == typeof(Text)) {
        string valText = reader.GetText();
        colResult.Add(new(cellType, valText, -1));

        if (colResult.Count >= colFilter.Count) {
          skip(typeof(Row));
          break;
        }
      }
    }

    return new(rowNum, colResult.ToArray());
  }

  LgExcelRow MakeResultRow(RowRead row, string[] sst) {
    var makeVal = (CellRead cell) => {
      var val = cell.Type switch {
        "n" => double.Parse(cell.Text),
        "s" => sst[cell.StrKey],
        _ => (object)cell.Text,
      };

      //if (val is string vvv) {
      //  if (string.IsNullOrEmpty(vvv)) {
      //    return 0;
      //  }
      //}

      return val;
    };

    return new(row.Cells.Select(makeVal).ToArray());
  }
}
